package com.mx.kn.cstf2.dao.impl;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import kn.cstf2.cop.Bean.TransitionsBeanCop;
import kn.cstf2.cop.Sql.CopSql;
import kn.cstf2.transition.Bean.TransitionBean;

import com.mx.kn.cstf2.dao.CopDao;
import com.mx.kn.cstf2.dto.ClientesSTKDto;
import com.mx.kn.cstf2.dto.CopAttachmentsDto;
import com.mx.kn.cstf2.dto.CopContactsDto;
import com.mx.kn.cstf2.dto.CopDto;
import com.mx.kn.cstf2.dto.CopWorkingInstructionsDto;
import com.mx.kn.cstf2.dto.CustomerSolutionDto;
import com.mx.kn.cstf2.dto.ExceptionCodeDto;
import com.mx.kn.cstf2.dto.KPIFieldsDto;
import com.mx.kn.cstf2.dto.LinksDto;
import com.mx.kn.cstf2.dto.UserClientsSTKDto;
import com.mx.kn.cstf2.dto.UsuarioDto;


@Repository
@Component("copDao")
public class CopDaoImpl implements CopDao{

	private JdbcTemplate jdbcStksf2;
	private JdbcTemplate jdbcCustomerSalesToolKit;

	@Autowired
	public void setDataSourceStkfsf2(@Qualifier("dataSourceStkfsf2") DataSource DataSourceStkfsf2) {
		this.jdbcStksf2 = new JdbcTemplate(DataSourceStkfsf2);
	}
	
	@Autowired
	public void setDataSourceCustomerSalesToolKit(@Qualifier("dataSourceCustomerSalesToolKit")
			DataSource dataSourceCustomerSalesToolKit) {
		this.jdbcCustomerSalesToolKit = new JdbcTemplate(dataSourceCustomerSalesToolKit);
		
	}
	
	@Override
	public void updateReportingTransition(TransitionBean transition) throws  SQLException{
		String sql =  CopSql.UPDATE_REPORTING_TRANSITIONS;
		int rows = jdbcStksf2
				.update(sql, new Object[] {transition.getReporting(), transition.getReportingdate(), transition.getId()});
	}
	
	@Override
	 public void updateScopeOfServiceTransition(TransitionBean transition) throws SQLException{
		 String sql = CopSql.UPDATE_SCOPEOFSERVICE_TRANSITIONS;
		 int rows = jdbcStksf2
					.update(sql, new Object[] {transition.getScopeofservice(), transition.getScopeofservicedate(), transition.getId()});
	 }

	@Override
	public  List<CopDto> getImports(TransitionBean transition) throws SQLException {
		String sql = "select * from client_operational_profile  WHERE export = 'f' and idtransitioncop = ?";
		List<CopDto> instancias = jdbcStksf2.query(
				sql, new Object[] {transition.getId()},new BeanPropertyRowMapper(CopDto.class));
		return instancias;
	}

	@Override
	public void addImport(TransitionBean transition, UsuarioDto usuario, Date fecha) throws SQLException {
		 String sql =  "INSERT INTO client_operational_profile (idtransitioncop, export, \"attachedFiles\", \"createdByUser\", \"creationDate\") VALUES ((SELECT id FROM t_transitions WHERE knkey = ? and \"tipoTransition\" = ?), FALSE, 0, ?, ?)";
		 int row = jdbcStksf2.update(sql, new Object[] { transition.getKnkey(),transition.getTipoTransition(),usuario.getUsername(), fecha});
	}

	@Override
	public List<CopDto> getExports(TransitionBean transition) throws SQLException {
		String sql = "select * from client_operational_profile  WHERE export = 't' and idtransitioncop = ?";
		List<CopDto> instancias = jdbcStksf2.query(
				sql, new Object[] {transition.getId()},new BeanPropertyRowMapper(CopDto.class));
		return instancias;
	}

	@Override
	public void addExport(TransitionBean transition, UsuarioDto usuario, Date fecha) throws SQLException {
		 String sql =  "INSERT INTO client_operational_profile (idtransitioncop, export, \"attachedFiles\", \"createdByUser\", \"creationDate\") VALUES ((SELECT id FROM t_transitions WHERE knkey = ? and \"tipoTransition\" = ?), TRUE, 0, ?, ?)";
		 int row = jdbcStksf2.update(sql, new Object[] { transition.getKnkey(),transition.getTipoTransition(), usuario.getUsername(), fecha});
		
	}

	@Override
	public void updateCop(CopDto datos) throws SQLException {
		String sql = "UPDATE client_operational_profile SET overview = ?, fcl = ?, lcl = ?, perishables = ?, brokerage = ?, intermodal = ?, insurance = ?, \"oceanFreight\" = ?," +
					"\"blueAnchorLine\" = ?, \"seaTransport\" = ?, \"exportCustom\" = ?, \"inbondExp\" = ?, \"importCustom\" = ?, \"inbondImp\" = ?, \"preCarrier\" = ?, \"allMotor\" = ?, combined = ?," +
					"\"onCarriage\" = ?, \"pickUpToActDep\" = ?, \"customClearToActDep\" = ?, \"actDepToActArriv\" = ?, \"actArrivToCustomClear\" = ?, \"actArrivToDelivery\" = ?, \"workInst\" = ?, requirements = ?" + 
					"WHERE id = ?";
		int rows = jdbcStksf2
				.update(sql, new Object[] {datos.getOverview(), datos.getFcl(), datos.getLcl(), datos.getPerishables(), datos.getBrokerage(), datos.getIntermodal(), 
						datos.getInsurance(), datos.getOceanFreight(), datos.getBlueAnchorLine(), datos.getSeaTransport(), datos.getExportCustom(), datos.getInbondExp(),
						datos.getImportCustom(), datos.getInbondImp(), datos.getPreCarrier(), datos.getAllMotor(), datos.getCombined(), datos.getOnCarriage(), datos.getPickUpToActDep(), 
						datos.getCustomClearToActDep(), datos.getActDepToActArriv(), datos.getActArrivToCustomClear(), datos.getActArrivToDelivery(), datos.getWorkInst(), 
						datos.getRequirements(), datos.getId() });
	}

	@Override
	public List<CopDto> getForm(Integer id) throws SQLException{
		String query = "SELECT * FROM client_operational_profile WHERE \"id\" = ?";
		List<CopDto> formulario = jdbcStksf2.query(
				query, new Object[] {id},new BeanPropertyRowMapper(CopDto.class));
		return formulario;
	}

	@Override
	public List<CopContactsDto> getContactsList(CopDto copDto)
			throws SQLException {
		String query = "SELECT * FROM cop_contacts WHERE \"idCop\" = ?";
		List<CopContactsDto> contactsCop = jdbcStksf2.query(
				query, new Object[] {copDto.getId()},new BeanPropertyRowMapper(CopContactsDto.class));
		return contactsCop;

}

	@Override
	public List<ClientesSTKDto> getClientInfo(Integer knkey)
			throws SQLException {
		
		String query = "SELECT * FROM t_clientes WHERE knkey = ?";
		List<ClientesSTKDto> datos = jdbcCustomerSalesToolKit.query(
				query, new Object[] {knkey},new BeanPropertyRowMapper(ClientesSTKDto.class));
		return datos;
		
	}

	@Override
	public List<UserClientsSTKDto> getBillingInstructions(Integer knkey)
			throws SQLException {
		
		String query = "select winuser from c_users_vs_clientes where customernum = ?";
		List<UserClientsSTKDto> billInst = jdbcCustomerSalesToolKit.query(
				query, new Object[] {knkey},new BeanPropertyRowMapper(UserClientsSTKDto.class));
		return billInst;
	}

	public void addContact(CopContactsDto contact) throws SQLException {
		String sql = "INSERT INTO cop_contacts (\"idCop\", \"name\", \"position\", \"company\", \"email\", \"phone\", \"ext\", \"mobile\") VALUES ( ?, ? , ?, ?, ?, ?, ?, ?)";
		int rows = jdbcStksf2
				.update(sql, new Object[] {contact.getIdCop(),contact.getName(),contact.getPosition(),contact.getCompany(),contact.getEmail(),contact.getPhone(),contact.getExt(),contact.getMobile()});
		
	}

	@Override
	public void addExcetionCode(ExceptionCodeDto exception) throws SQLException {
		String sql = "INSERT INTO cop_exception (\"idCop\", \"exceptionCode\", \"exceptionLogic\", \"description\") VALUES (?, ?, ?, ?)";
		int rows = jdbcStksf2
				.update(sql, new Object[] {exception.getIdCop(),exception.getExceptionCode(),exception.getExceptionLogic(),exception.getDescription()});
	}

	@Override
	public List<ExceptionCodeDto> getExceptionsByIdCop(
			ExceptionCodeDto exception) throws SQLException {
		String sql = "SELECT * FROM \"public\".\"cop_exception\"  where \"idCop\" = ?";
		List<ExceptionCodeDto> exceptions = jdbcStksf2.query(
				sql, new Object[] {exception.getIdCop()},new BeanPropertyRowMapper(ExceptionCodeDto.class));
		return exceptions;

	}

	@Override
	public List<CustomerSolutionDto> getReports(Integer knkey)
			throws SQLException {
		String query = "SELECT * FROM t_customer_solution WHERE knkey = ?";
		List<CustomerSolutionDto> reports = jdbcCustomerSalesToolKit.query(
				query, new Object[] {knkey},new BeanPropertyRowMapper(CustomerSolutionDto.class));
		return reports;
	}

	@Override
	public List<KPIFieldsDto> getKPIFields(Integer idCop) throws SQLException {
		String query = "SELECT * FROM cop_kpi_fields WHERE \"idCop\" = ? ORDER BY \"fieldValue\"";
		List<KPIFieldsDto> fields = jdbcStksf2.query(
				query, new Object[] {idCop},new BeanPropertyRowMapper(KPIFieldsDto.class));
		return fields;
	}

	@Override
	public void updateKPIFields(KPIFieldsDto updateKPI)
			throws SQLException {
		String sql = "UPDATE cop_kpi_fields SET \"fieldValue\" = ? WHERE \"id\" = ?";	
		int rows = jdbcStksf2
				.update(sql, new Object[] {updateKPI.getFieldValue(), updateKPI.getId()});
	}

	@Override
	public void updateKPIChecks(KPIFieldsDto updateKPI) throws SQLException {
		String sql = "UPDATE cop_kpi_fields SET \"checked\" = ? WHERE \"id\" = ?";	
		int rows = jdbcStksf2
				.update(sql, new Object[] {updateKPI.getChecked(), updateKPI.getId()});
		
	}

	@Override
	public void insertKPI(KPIFieldsDto insertKPI) throws SQLException {
		String sql = "INSERT INTO cop_kpi_fields (\"idCop\") VALUES (?)";
		int rows = jdbcStksf2
				.update(sql, new Object[] {insertKPI.getIdCop()});
	}

	@Override
	public List<CopWorkingInstructionsDto> getWorkInstFieldsSecCase(
			Integer tipoModulo) throws SQLException {
		String query = "SELECT \"id\", \"fieldValue\" "+
						"FROM cop_working_instructions_fields "+
						"WHERE \"idTipoModulo\" = ? "+
						"ORDER BY \"id\"";
		List<CopWorkingInstructionsDto> fields = jdbcStksf2.query(
				query, new Object[] {tipoModulo},new BeanPropertyRowMapper(CopWorkingInstructionsDto.class));
		return fields;
	}
	
	@Override
	public List<CopWorkingInstructionsDto> getWorkInstFields(
			Integer tipoModulo, Integer idCop) throws SQLException {
		String query = "SELECT cop_working_instructions_fields.\"id\", cop_working_instructions_fields.\"fieldValue\", "+
						"cop_working_instructions_values.\"inputValue\" "+
						"FROM cop_working_instructions_fields "+
						"INNER JOIN cop_working_instructions_values "+
						"ON cop_working_instructions_fields.\"id\" = cop_working_instructions_values.\"idWorkInstField\" "+
						"WHERE cop_working_instructions_fields.\"idTipoModulo\" = ? "+
						"AND cop_working_instructions_values.\"idCop\" = ? "+
						"ORDER BY cop_working_instructions_fields.\"fieldValue\"";
		List<CopWorkingInstructionsDto> fields = jdbcStksf2.query(
				query, new Object[] {tipoModulo, idCop},new BeanPropertyRowMapper(CopWorkingInstructionsDto.class));
		return fields;
	}

	@Override
	public void updateWorkInstFields(Integer idWorkInst, String textValue, Integer idCop)
			throws SQLException {
		String sql = "UPDATE cop_working_instructions_values SET \"inputValue\" = ? WHERE \"idWorkInstField\" = ? AND \"idCop\" = ?";	
		int rows = jdbcStksf2
				.update(sql, new Object[] {textValue, idWorkInst, idCop});
		
	}

	@Override
	public int getconsecutiveNameFile() throws SQLException {
		String sql = "select nextval('seq_cop_attach_name')";	
		return jdbcStksf2.queryForObject(sql,Integer.class);
//		return 0;
	}

	@Override
	public void saveNameAttachment(CopAttachmentsDto attachment)
			throws SQLException {
		String sql = "INSERT INTO cop_attachments (\"idCop\" , \"archiveName\") VALUES (?,  ?)";
		int rows = jdbcStksf2
				.update(sql, new Object[] {attachment.getIdCop(),attachment.getArchiveName()});
	}

	@Override
	public List<CopAttachmentsDto> getAttachmentName(
			CopAttachmentsDto attachment) throws SQLException {
		String query = "SELECT * from cop_attachments  where \"idCop\" = ?";
		List<CopAttachmentsDto> fields = jdbcStksf2.query(
				query, new Object[] {attachment.getIdCop()},new BeanPropertyRowMapper(CopAttachmentsDto.class));
		return fields;
	}

	@Override
	public void deleteAttachment(CopAttachmentsDto attachment)
			throws SQLException {
		String sql="DELETE FROM cop_attachments WHERE id = ? ";
		int rows = jdbcStksf2.update(sql,new Object[] {attachment.getId()});
//		
		
	}

	@Override
	public void deleteContactCop(CopContactsDto contact)
			throws SQLException {
		String sql="DELETE FROM cop_contacts WHERE id = ? ";
		int rows = jdbcStksf2.update(sql,new Object[] {contact.getId()});
//		
		
	}

	@Override
	public List<CopContactsDto> getContactsListMail(long idTransition)
			throws SQLException {
	
		String query = "select cop_contacts.* from client_operational_profile " +
						" INNER JOIN cop_contacts" +
						" on (cop_contacts.\"idCop\" = client_operational_profile.\"id\")"+
						" where client_operational_profile.idtransitioncop = ?";
		List<CopContactsDto> fields = jdbcStksf2.query(
				query, new Object[] {idTransition},new BeanPropertyRowMapper(CopContactsDto.class));
		return fields;
	}

	@Override
	public List<CopContactsDto> getNotifyStatusCop(Integer idCop)
			throws SQLException {
		String query = "select email from cop_contacts WHERE \"idCop\" = ?";
		
		List<CopContactsDto> fields = jdbcStksf2.query(
				query, new Object[] {idCop},new BeanPropertyRowMapper(CopContactsDto.class));
		return fields;
	}

	@Override
	public List<CopWorkingInstructionsDto> validaExistenciaCop(Integer idCop) throws SQLException {
		String query = "SELECT \"id\", \"inputValue\" FROM cop_working_instructions_values WHERE \"idCop\" = ?";
		
		List<CopWorkingInstructionsDto> fields = jdbcStksf2.query(
				query, new Object[] {idCop},new BeanPropertyRowMapper(CopWorkingInstructionsDto.class));
		return fields;
	}

	@Override
	public void insertWorkInstFields(Integer idWorkInst,
			Integer idCop) throws SQLException {
		String sql = "INSERT INTO cop_working_instructions_values (\"idCop\", \"idWorkInstField\") VALUES (?, ?)";
		int rows = jdbcStksf2
				.update(sql, new Object[] {idCop, idWorkInst});
		
	}
	

}
